﻿using DBUtil.MetaData;
using DBUtil.Provider.SqlServer.MetaData;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Index = DBUtil.MetaData.Index;

namespace DBUtil.Provider.SqlServer
{
    public partial class SqlServerManage
    {
        #region 表的元数据
        /// <summary>
        /// 返回所有的表
        /// </summary>
        /// <param name="schemaName">指定schema名称，如果为空则返回所有模式的表</param>
        /// <param name="isDetail">是否给表填充详细信息,默认不填充</param>
        public override List<Table> ShowTables(string schemaName, bool isDetail = false)
        {
            schemaName = db.DealSqlInject(schemaName);
            string sql = $@"
select t.*,o.create_date,o.modify_date, e.value from INFORMATION_SCHEMA.TABLES t
left join sys.objects o on OBJECT_ID(t.TABLE_NAME)=o.object_id 
left join sys.extended_properties e on OBJECT_ID(t.TABLE_NAME)=e.major_id and e.minor_id=0 and e.name='MS_Description'
 where t.TABLE_TYPE='BASE TABLE'";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace()) sql += $" and t.TABLE_SCHEMA='{schemaName}'";
            string sql_size = @"
if object_id(N'tempdb..#tmp_tbl_size',N'U') is not null
    DROP table #tmp_tbl_size;
create table #tmp_tbl_size(
	name varchar(255),
	rows bigint,
	reserved varchar(20),
	data varchar(50),
	index_size varchar(50),
	unused varchar(20)
)
truncate table #tmp_tbl_size
exec sp_MSforeachtable 'insert into #tmp_tbl_size exec sp_spaceused ''?'''
select * from #tmp_tbl_size";
            var dt = db.SelectDataTable(sql);
            var dt2 = db.SelectDataTable(sql_size);
            var tables = new List<Table>();
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var tbl = new SqlServerTable()
                {
                    SchemaName = dt.Rows[i]["TABLE_SCHEMA"].ToString(),
                    Name = dt.Rows[i]["TABLE_NAME"].ToString(),
                    Desc = dt.Rows[i]["value"].ToString()
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    tbl.CreateTime = DateTime.Parse(createTime);
                }
                var updatetime = dt.Rows[i]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    tbl.LastUpdate = DateTime.Parse(updatetime);
                }
                tables.Add(tbl);
                //有些表不能通过sp_MSforeachtable访问到,比如:cdc.captured_columns等系列表(sql变更数据捕获)
                DataRow datarow = null;
                for (int j = 0; j < dt2.Rows.Count; j++)
                {
                    var tblname = dt2.Rows[j]["name"].ToString();
                    if (tblname == tbl.Name || tblname == $"[{tbl.SchemaName}].[{tbl.Name}]")
                    {
                        datarow = dt2.Rows[j];
                        break;
                    }
                }
                if (datarow == null)
                {
                    //处理sp_MSforeachtable不能访问到的表
                    var _tmpdt = db.SelectDataTable($"exec sp_spaceused '[{tbl.SchemaName}].[{tbl.Name}]'");
                    if (_tmpdt.Rows.Count > 0)
                    {
                        datarow = _tmpdt.Rows[0];
                    }
                }
                if (datarow != null)
                {
                    tbl.RowCount = int.Parse(datarow["rows"].ToString());
                    tbl.ReservedSize = datarow["reserved"].ToString();
                    try
                    {
                        if (tbl.ReservedSize.EndsWith(" KB"))
                        {
                            var size = tbl.ReservedSize.Replace(" KB", "").To<int>() * 1024;
                            if (size > 1024) tbl.ReservedSize = DotNetCommon.UnitConverter.Humanize(size);
                        }
                    }
                    catch { }
                    tbl.DataSize = datarow["data"].ToString();
                    tbl.IndexSize = datarow["index_size"].ToString();
                    tbl.UnUsedSize = datarow["unused"].ToString();
                    #region sql变更数据捕获表自动赋予说明
                    if (tbl.Desc.IsNullOrEmptyOrWhiteSpace())
                    {
                        switch (tbl.SchemaName + "." + tbl.Name)
                        {
                            case "cdc.captured_columns":
                            case "cdc.change_tables":
                            case "cdc.ddl_history":
                            case "cdc.index_columns":
                            case "cdc.lsn_time_mapping":
                            case "dbo.systranschemas":
                                {
                                    tbl.Desc = "[自动说明: 变更数据捕获用表]";
                                    break;
                                }
                        }
                        if (tbl.Desc.IsNullOrEmptyOrWhiteSpace())
                        {
                            if (tbl.SchemaName == "cdc" && tbl.Name.EndsWith("_CT"))
                            {
                                var instname = tbl.Name.Substring(0, tbl.Name.Length - 3);
                                var count = db.SelectScalar<int>($"select count(1) from cdc.change_tables where capture_instance='{instname}'");
                                if (count == 1)
                                {
                                    if (instname.IndexOf('_') > 0)
                                    {
                                        var index = instname.IndexOf('_');
                                        instname = instname.Substring(0, index) + "." + instname.Substring(index + 1);
                                    }
                                    tbl.Desc = $"[自动说明: 变更数据捕获实例表({instname})]";
                                }
                            }
                        }
                    }
                    #endregion
                }
                if (isDetail)
                {
                    FillTableDetail(tbl, false);
                }
            }
            return tables;
        }
        /// <summary>
        /// 返回指定表的详细元数据
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="schemaName">表模式名称，如果为空则返回找到的第一个表</param>
        public override Table ShowTableDetail(string tableName, string schemaName = null)
        {
            tableName = db.DealSqlInject(tableName);
            schemaName = db.DealSqlInject(schemaName);
            if (string.IsNullOrWhiteSpace(tableName)) return null;
            var objName = db.ParseObjectName(tableName, schemaName);
            return FillTableDetail(new SqlServerTable() { Name = objName.Name, SchemaName = objName.SchemaName }, true);
        }
        /// <summary>
        /// 给表填充详细元数据信息
        /// </summary>
        /// <param name="table">表</param>
        /// <param name="filltablesimpleinfo">是否需要填充表简要信息</param>
        private Table FillTableDetail(SqlServerTable table, bool filltablesimpleinfo)
        {
            if (filltablesimpleinfo) FillTableSimpleInfo(table);
            FillColumns(table);
            table.Triggers = showTableTriggers(table.Name, table.SchemaName);
            table.Constraints = showTableConstraints(table.Name, table.SchemaName);
            table.Indexes = showTableIndexes(table.Name, table.SchemaName);
            ModifyColProperties(table);
            ModifyTableProperties(table);
            return table;
        }
        /// <summary>
        /// 根据表名填充表说明/创建时间/更新时间/架构
        /// </summary>
        /// <param name="table"></param>
        private void FillTableSimpleInfo(SqlServerTable table)
        {
            if (table == null) return;
            string sql = $@"
select t.*,o.create_date,o.modify_date, e.value from INFORMATION_SCHEMA.TABLES t
left join sys.objects o on OBJECT_ID(t.TABLE_NAME)=o.object_id 
left join sys.extended_properties e on  OBJECT_ID(t.TABLE_NAME)=e.major_id and e.minor_id=0 and e.name='MS_Description'
 where t.TABLE_TYPE='BASE TABLE' and t.TABLE_NAME='{table.Name}'";
            if (!string.IsNullOrWhiteSpace(table.SchemaName))
            {
                sql += $" and t.TABLE_SCHEMA='{table.SchemaName}'";
            }
            DataTable dt = db.SelectDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                table.Name = dt.Rows[0]["TABLE_NAME"].ToString();
                table.Desc = dt.Rows[0]["value"].ToString();
                var createTime = dt.Rows[0]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    table.CreateTime = DateTime.Parse(createTime);
                }
                table.SchemaName = dt.Rows[0]["TABLE_SCHEMA"].ToString();
                var updatetime = dt.Rows[0]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    table.LastUpdate = DateTime.Parse(updatetime);
                }
                sql = $"exec sp_spaceused '{table.SchemaName}.{table.Name}'";
                var dt2 = db.SelectDataTable(sql);
                table.RowCount = int.Parse(dt2.Rows[0]["rows"].ToString());
                table.ReservedSize = dt2.Rows[0]["reserved"].ToString();
                table.DataSize = dt2.Rows[0]["data"].ToString();
                table.IndexSize = dt2.Rows[0]["index_size"].ToString();
                table.UnUsedSize = dt2.Rows[0]["unused"].ToString();
            }
        }

        /// <summary>
        /// 返回指定表的所有列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName">表模式名称，如果为空则返回找到的第一个表</param>
        /// <returns></returns>
        public override List<Column> ShowTableColumns(string tableName, string schemaName = null)
        {
            tableName = db.DealSqlInject(tableName);
            schemaName = db.DealSqlInject(schemaName);
            if (string.IsNullOrWhiteSpace(tableName)) return null;
            var objName = db.ParseObjectName(tableName, schemaName);
            var table = new SqlServerTable()
            {
                Name = objName.Name,
                SchemaName = objName.SchemaName
            };
            FillTableSimpleInfo(table);
            FillColumns(table);
            return table.Columns;
        }

        /// <summary>
        /// 根据表名填充列信息
        /// </summary>
        /// <param name="table">要进行填充的表</param>
        private void FillColumns(Table table)
        {
            if (table == null) return;
            string sql = $@"

select 
	序号=ROW_NUMBER() OVER(order BY ORDINAL_POSITION),
	列名=T.COLUMN_NAME,
	类型=T.DATA_TYPE,
    T.CHARACTER_MAXIMUM_LENGTH,
	T.NUMERIC_PRECISION,
	T.NUMERIC_PRECISION_RADIX,
	T.NUMERIC_SCALE,
	T.DATETIME_PRECISION,
	说明=tt.说明,
	是否可空=T.IS_NULLABLE,
    自增种子=IDENT_SEED('{table.SchemaName}.{table.Name}'),
    增量=IDENT_INCR('{table.SchemaName}.{table.Name}'),
	默认值=T.COLUMN_DEFAULT,
	c.是否自增,
    tt.is_computed,
    tt.definition,
    tt.is_persisted    
from INFORMATION_SCHEMA.COLUMNS T
--关联列说明
left outer join
(select 列名=c.name,说明=p.value ,cc.is_computed,cc.definition,cc.is_persisted
from sys.columns c 
    left outer join sys.computed_columns cc on c.object_id=cc.object_id and c.column_id=cc.column_id
	left outer join sys.extended_properties p on c.column_id = p.minor_id and c.object_id=p.major_id
where c.object_id=OBJECT_ID('{table.SchemaName}.{table.Name}'))  tt
on t.COLUMN_NAME=tt.列名
--关联自增信息
left outer join
(SELECT TABLE_NAME,COLUMN_NAME,TABLE_SCHEMA, 是否自增=case (COLUMNPROPERTY(      
      OBJECT_ID('{table.SchemaName}.{table.Name}'),COLUMN_NAME,'IsIdentity')) when 1 then 'Yes' else 'No' end  FROM INFORMATION_SCHEMA.columns) c
on c.COLUMN_NAME =T.COLUMN_NAME and c.TABLE_NAME=t.TABLE_NAME and c.TABLE_SCHEMA=t.TABLE_SCHEMA
where t.TABLE_NAME='{table.Name}' and t.TABLE_SCHEMA='{table.SchemaName}'
and c.TABLE_NAME='{table.Name}'";
            DataTable dt = db.SelectDataTable(sql);
            List<Column> columns = new List<Column>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    var column = new SqlServerColumn();
                    column.Name = dt.Rows[i]["列名"].ToString();
                    column.TableName = table.Name;
                    //列类型
                    column.DATA_TYPE = dt.Rows[i]["类型"].ToString();
                    column.CHARACTER_MAXIMUM_LENGTH = dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    column.NUMERIC_PRECISION = dt.Rows[i]["NUMERIC_PRECISION"].ToString();
                    column.DATETIME_PRECISION = dt.Rows[i]["DATETIME_PRECISION"].ToString();
                    column.NUMERIC_SCALE = dt.Rows[i]["NUMERIC_SCALE"].ToString();
                    column.ConvertType();
                    //列说明
                    column.Desc = dt.Rows[i]["说明"].ToString();
                    //是否可空
                    column.IsNullAble = dt.Rows[i]["是否可空"].ToString() == "NO" ? false : true;
                    //自增
                    column.IsIdentity = dt.Rows[i]["是否自增"].ToString() == "No" ? false : true;
                    if (column.IsIdentity)
                    {
                        column.IdentityStart = int.Parse(dt.Rows[i]["自增种子"].ToString());
                        column.IdentityIncre = int.Parse(dt.Rows[i]["增量"].ToString());
                    }
                    //默认值
                    var de = dt.Rows[i]["默认值"].ToString();
                    column.HasDefault = string.IsNullOrEmpty(de) ? false : true;
                    //计算列
                    column.IsComputed = dt.Rows[i]["is_computed"].ToString() == "True";
                    if (column.IsComputed)
                    {
                        column.ComputedDefinition = dt.Rows[i]["definition"].ToString();
                        column.IsPersisted = dt.Rows[i]["is_persisted"].ToString() == "True";
                    }
                    if (column.HasDefault) column.Default = dt.Rows[i]["默认值"].ToString();
                    columns.Add(column);
                    //唯一:通过唯一约束修正
                }
            }
            table.Columns = columns;
            sql = $@"
 select b.column_name
from information_schema.table_constraints a
inner join information_schema.constraint_column_usage b
on a.constraint_name = b.constraint_name
where a.constraint_type = 'PRIMARY KEY' and a.table_name = '{table.Name}' and a.TABLE_SCHEMA='{table.SchemaName}'
";
            dt = db.SelectDataTable(sql);
            table.PrimaryKeyColumns = new List<Column>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string colname = dt.Rows[i][0].ToString();
                    table.PrimaryKey += "," + colname;
                    var col = table.Columns.First<Column>(j => j.Name == colname);
                    table.PrimaryKeyColumns.Add(col);
                    col.IsPrimaryKey = true;
                    if (dt.Rows.Count == 1)
                    {
                        col.IsUnique = true;
                    }
                }
            }
            table.PrimaryKey = (table.PrimaryKey ?? "").Trim(',');
        }

        /// <summary>
        /// 返回指定表的所有约束
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<DBUtil.MetaData.Constraint> ShowTableConstraints(string tableName, string schemaName = null)
        {
            tableName = db.DealSqlInject(tableName);
            schemaName = db.DealSqlInject(schemaName);
            return showTableConstraints(tableName, schemaName);
        }

        public List<DBUtil.MetaData.Constraint> showTableConstraints(string tableName, string schemaName = null)
        {
            var objName = db.ParseObjectName(tableName, schemaName);
            var constraints = new List<DBUtil.MetaData.Constraint>();
            var sql = "";
            //查询默认约束
            sql = $@"
select 
SCHEMA_NAME(sysd.schema_id) as schema_name,
syst.name as table_name,
sysd.name,
sysd.type,sysd.type_desc,
sysc.name as column_name,
sysd.create_date,sysd.modify_date,
sysd.is_system_named,sysd.definition 
from sys.default_constraints sysd 
left join sys.tables syst on syst.object_id=sysd.parent_object_id
left join sys.columns sysc on syst.object_id=sysc.object_id and sysc.column_id=sysd.parent_column_id
where sysd.type='D' 
and sysd.parent_object_id=OBJECT_ID('{objName.NormalName}')
";
            var dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var cons = new SqlServerConstraint()
                {
                    SchemaName = dt.Rows[i]["schema_name"].ToString(),
                    TableName = dt.Rows[i]["table_name"].ToString(),
                    Name = dt.Rows[i]["name"].ToString(),
                    Type = EnumConstraintType.DefaultValue,
                    Constraintkeys = dt.Rows[i]["column_name"].ToString(),
                    IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                    Definition = dt.Rows[i]["definition"].ToString()
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    cons.CreateTime = DateTime.Parse(createTime);
                }
                if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                {
                    cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                }
                constraints.Add(cons);
            }
            //查询检查约束
            sql = $@"
select 
	SCHEMA_NAME(sysc.schema_id) as schema_name,
	syst.name as table_name, 
	sysc.name,
	sysc.create_date,sysc.modify_date,
	sysc.type,sysc.type_desc,
	sysc.definition,sysc.is_system_named
from sys.check_constraints sysc left join sys.tables syst on sysc.parent_object_id=syst.object_id
where 
	sysc.parent_object_id =OBJECT_ID('{objName.NormalName}')";
            dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var cons = new SqlServerConstraint()
                {
                    SchemaName = dt.Rows[i]["schema_name"].ToString(),
                    TableName = dt.Rows[i]["table_name"].ToString(),
                    Name = dt.Rows[i]["name"].ToString(),
                    Type = EnumConstraintType.Check,
                    IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                    Definition = dt.Rows[i]["definition"].ToString()
                };

                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    cons.CreateTime = DateTime.Parse(createTime);
                }
                if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                {
                    cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                }
                constraints.Add(cons);
            }
            //查询主键约束和唯一约束
            sql = $@"
select 
	sysk.object_id,SCHEMA_NAME(sysk.schema_id) as schema_name,sysk.name as name, syst.name as table_name,sysc.name as col_name,
	sysk.type,sysk.type_desc,
	sysk.create_date,sysk.modify_date,
	sysk.is_system_named
from sys.key_constraints sysk 
	left join sys.indexes sysi on sysk.name =sysi.name
	left join sys.index_columns sysic on sysi.object_id=sysic.object_id and sysi.index_id=sysic.index_id
	left join sys.columns sysc on sysic.object_id=sysc.object_id and sysic.column_id=sysc.column_id
	left join sys.tables syst on sysi.object_id=syst.object_id
where sysk.parent_object_id =object_id('{objName.NormalName}')
order by sysk.object_id";
            dt = db.SelectDataTable(sql);
            int lastobjid = -1;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var objid = int.Parse(dt.Rows[i]["object_id"].ToString());
                var colname = dt.Rows[i]["col_name"].ToString();
                if (lastobjid == objid)
                {
                    constraints.Last().Constraintkeys += "," + colname;
                    continue;
                }
                lastobjid = objid;
                var cons = new SqlServerConstraint()
                {
                    SchemaName = dt.Rows[i]["schema_name"].ToString(),
                    TableName = dt.Rows[i]["table_name"].ToString(),
                    Name = dt.Rows[i]["name"].ToString(),
                    Constraintkeys = colname,
                    Type = dt.Rows[i]["type"].ToString() == "PK" ? EnumConstraintType.PrimaryKey : EnumConstraintType.Unique,
                    IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True"
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    cons.CreateTime = DateTime.Parse(createTime);
                }
                if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                {
                    cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                }
                constraints.Add(cons);
            }
            //查询外键约束
            sql = $@"
select 
	SCHEMA_NAME(sysfk.schema_id) as schema_name,
	sysfk.name,
	srctablename=(select name from sys.tables syst where sysfkc.parent_object_id=syst.object_id),
	srccolname=(select sysc.name from sys.columns sysc where sysfkc.parent_column_id=sysc.column_id and sysfkc.parent_object_id=sysc.object_id),
	desttablename=(select name from sys.tables syst where sysfkc.referenced_object_id=syst.object_id),
	destcolname=(select sysc.name from sys.columns sysc where sysfkc.referenced_column_id=sysc.column_id and sysfkc.referenced_object_id=sysc.object_id),
	sysfk.create_date,
	sysfk.modify_date,
	sysfk.is_system_named,
	sysfk.delete_referential_action,
	sysfk.delete_referential_action_desc,
	sysfk.update_referential_action,
	sysfk.update_referential_action_desc
from sys.foreign_keys sysfk 
	left join sys.foreign_key_columns sysfkc on sysfk.object_id=sysfkc.constraint_object_id
	left join sys.tables syst on sysfk.parent_object_id=syst.object_id
	left join sys.tables systr on sysfk.referenced_object_id=systr.object_id
	left join sys.columns sysc on sysc.object_id =sysfk.parent_object_id and sysfk.key_index_id=sysc.column_id
where sysfk.parent_object_id =object_id('{objName.NormalName}')";
            dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var cons = new SqlServerConstraint()
                {
                    SchemaName = dt.Rows[i]["schema_name"].ToString(),
                    TableName = dt.Rows[i]["srctablename"].ToString(),
                    Name = dt.Rows[i]["name"].ToString(),
                    Constraintkeys = dt.Rows[i]["srccolname"].ToString(),
                    ReferenceTableName = dt.Rows[i]["desttablename"].ToString(),
                    ReferenceColumnName = dt.Rows[i]["destcolname"].ToString(),
                    Type = EnumConstraintType.ForeignKey,
                    IsSystemNamed = dt.Rows[i]["is_system_named"].ToString() == "True",
                    Delete_Action = dt.Rows[i]["delete_referential_action_desc"].ToString(),
                    Update_Action = dt.Rows[i]["update_referential_action_desc"].ToString()
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    cons.CreateTime = DateTime.Parse(createTime);
                }
                if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                {
                    cons.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                }
                constraints.Add(cons);
            }
            return constraints;
        }

        /// <summary>
        /// 返回指定表所有索引
        /// </summary>
        /// <param name="tableName">所属的表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<Index> ShowTableIndexes(string tableName, string schemaName = null)
        {
            tableName = db.DealSqlInject(tableName);
            schemaName = db.DealSqlInject(schemaName);
            return showTableIndexes(tableName, schemaName);
        }

        private List<Index> showTableIndexes(string tableName, string schemaName = null)
        {
            var objName = db.ParseObjectName(tableName, schemaName);
            List<Index> indexs = new List<Index>();
            string sql = $@"
select 
OBJECT_SCHEMA_NAME(sysi.object_id) as 'schema',
sysd.name  as 'data_space_name', 
sysd.type as 'data_space_type',
sysd.type_desc as 'data_space_type_desc',
sysi.name as '索引名称', 
syst.name as '表名',
sysc.name as '列名',
sysi.type_desc as '索引类型',
sysi.is_unique as '索引是否要求唯一',
sysc.is_nullable as '索引是否允许为空',
sysi.is_primary_key as '是否是主键约束',
sysi.is_unique_constraint as '是否是唯一约束',
sysk.create_date,
sysk.modify_date
from sys.indexes sysi 
left join sys.key_constraints sysk on sysi.object_id=sysk.parent_object_id and sysi.name=sysk.name
left join sys.index_columns sysic on sysi.object_id=sysic.object_id and sysi.index_id=sysic.index_id
left join sys.tables syst on sysi.object_id=syst.object_id
left join sys.columns sysc on sysi.object_id=sysc.object_id and sysic.column_id=sysc.column_id
left join sys.data_spaces sysd on sysi.data_space_id=sysd.data_space_id

where sysi.object_id=OBJECT_ID('{objName.NormalName}')
order by sysi.name
";
            var dt = db.SelectDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                SqlServerIndex lastindex = null;
                for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    var tablename = dt.Rows[i]["表名"].ToString();
                    var colname = dt.Rows[i]["列名"].ToString();
                    var indexname = dt.Rows[i]["索引名称"].ToString();
                    if (lastindex != null && lastindex.TableName == tablename && lastindex.Name == indexname)
                    {
                        lastindex.ColumnNames += "," + colname;
                        continue;
                    }
                    else if (lastindex != null)
                    {
                        indexs.Add(lastindex);
                    }
                    lastindex = new SqlServerIndex()
                    {
                        DataBaseName = db.DBName,
                        SchemaName = dt.Rows[i]["schema"].ToString(),
                        Name = dt.Rows[i]["索引名称"].ToString(),
                        DataSpaceName = dt.Rows[i]["data_space_name"].ToString(),
                        DataSpaceType = dt.Rows[i]["data_space_type"].ToString(),
                        DataSpaceTypeDesc = dt.Rows[i]["data_space_type_desc"].ToString(),
                        TableName = dt.Rows[i]["表名"].ToString(),
                        ColumnNames = dt.Rows[i]["列名"].ToString(),
                        IsClustered = dt.Rows[i]["索引类型"].ToString() == "CLUSTERED",
                        IndexType = dt.Rows[i]["索引类型"].ToString(),
                        IsNullAble = dt.Rows[i]["索引是否允许为空"].ToString() == "True",
                        IsUnique = dt.Rows[i]["索引是否要求唯一"].ToString() == "True",
                        IsPrimaryKey = dt.Rows[i]["是否是主键约束"].ToString() == "True",
                        IsUniqueKey = dt.Rows[i]["是否是唯一约束"].ToString() == "True"
                    };
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i]["create_date"].ToString()))
                    {
                        lastindex.CreateTime = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                    }
                    if (!string.IsNullOrWhiteSpace(dt.Rows[i]["modify_date"].ToString()))
                    {
                        lastindex.LastUpdate = DateTime.Parse(dt.Rows[i]["modify_date"].ToString());
                    }
                }
                indexs.Add(lastindex);
            }
            return indexs;
        }
        /// <summary>
        /// 返回触发器集合
        /// </summary>
        /// <param name="tableName">所属的表名称</param>
        /// <param name="schemaName">模式名称</param>
        /// <returns></returns>
        public override List<Trigger> ShowTableTriggers(string tableName, string schemaName = null)
        {
            tableName = db.DealSqlInject(tableName);
            schemaName = db.DealSqlInject(schemaName);
            return showTableTriggers(tableName, schemaName);
        }

        public List<Trigger> showTableTriggers(string tableName, string schemaName = null)
        {
            if (string.IsNullOrWhiteSpace(tableName)) return new List<Trigger>();
            var objName = db.ParseObjectName(tableName, schemaName);
            List<Trigger> triggers = new List<Trigger>();
            var sql = $@"
select syst.name as tablename, 
	systri.name as trigger_name,
	trigger_owner = user_name(ObjectProperty( systri.object_id, 'ownerid')),
	trigger_schema = schema_name(syso.schema_id),
	systri.create_date,
	systri.modify_date,
	systri.is_disabled,
	systri.is_not_for_replication,
	systri.is_instead_of_trigger,	
	isupdate = ObjectProperty( systri.object_id, 'ExecIsUpdateTrigger'),
	isdelete = ObjectProperty( systri.object_id, 'ExecIsDeleteTrigger'),
	isinsert = ObjectProperty( systri.object_id, 'ExecIsInsertTrigger'),
	isafter = ObjectProperty( systri.object_id, 'ExecIsAfterTrigger'),
	isinsteadof = ObjectProperty( systri.object_id, 'ExecIsInsteadOfTrigger'),
	createsql=OBJECT_DEFINITION(systri.object_id)	
from sys.triggers systri 
left join sys.tables syst on systri.parent_id=syst.object_id
left join sys.objects syso on systri.object_id=syso.object_id
where systri.parent_id=object_id('{objName.NormalName}')
";
            DataTable dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                string name = dt.Rows[i]["trigger_name"].ToString();
                schemaName = dt.Rows[i]["trigger_owner"].ToString();
                bool isupdate = dt.Rows[i]["isupdate"].ToString() == "0" ? false : true;
                bool isdelete = dt.Rows[i]["isdelete"].ToString() == "0" ? false : true;
                bool isinsert = dt.Rows[i]["isinsert"].ToString() == "0" ? false : true;
                bool isafter = dt.Rows[i]["isafter"].ToString() == "0" ? false : true;
                bool isinsteadof = dt.Rows[i]["isInsteadof"].ToString() == "0" ? false : true;
                bool is_disabled = dt.Rows[i]["is_disabled"].ToString() == "False" ? false : true;
                bool is_not_for_replication = dt.Rows[i]["is_not_for_replication"].ToString() == "False" ? false : true;
                string createsql = dt.Rows[i]["createsql"].ToString();
                Trigger tri = new SqlServerTrigger()
                {
                    Name = name,
                    CreateSql = createsql,
                    TableName = tableName,
                    IsInsert = isinsert,
                    IsUpdate = isupdate,
                    IsDelete = isdelete,
                    IsAfter = isafter,
                    IsInsteadof = isinsteadof,
                    IsDisable = is_disabled,
                    IsNotForReplication = is_not_for_replication
                };
                var createTime = dt.Rows[0]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    tri.CreateTime = DateTime.Parse(createTime);
                }
                var updatetime = dt.Rows[0]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    tri.LastUpdate = DateTime.Parse(updatetime);
                }
                triggers.Add(tri);
            }
            return triggers;
        }
        /// <summary>
        /// 根据表的约束和索引信息,修改表的列信息
        /// </summary>
        /// <param name="table">表</param>
        private void ModifyColProperties(Table table)
        {
            if (table.Columns.IsNullOrEmpty()) return;
            if (table.Constraints.IsNotNullOrEmpty())
            {
                for (var i = 0; i < table.Constraints.Count; i++)
                {
                    var constraint = table.Constraints[i];
                    if (constraint.Type == EnumConstraintType.Unique)
                    {
                        if (!constraint.Constraintkeys.Contains(","))
                        {
                            var column = table.Columns.FirstOrDefault(col => col.Name == constraint.Constraintkeys);
                            column.IsUnique = true;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 根据索引信息修改表属性
        /// </summary>
        /// <param name="table"></param>
        private void ModifyTableProperties(SqlServerTable table)
        {
            SqlServerIndex index = null;
            index = table.Indexes.FirstOrDefault(i => i.IsClustered || i.IndexType == "HEAP") as SqlServerIndex;
            if (index != null)
            {
                table.DataSpaceName = index.DataSpaceName;
                table.DataSpaceType = index.DataSpaceType;
                table.DataSpaceTypeDesc = index.DataSpaceTypeDesc;
            }
        }
        #endregion

        #region 视图元数据
        /// <summary>
        /// 返回指定视图的详细元数据
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="schemaName">所属模式名称,如果为空则返回第一个</param>
        public override View ShowViewDetail(string viewName, string schemaName = null)
        {
            viewName = db.DealSqlInject(viewName);
            schemaName = db.DealSqlInject(schemaName);
            if (string.IsNullOrWhiteSpace(viewName)) return null;
            var objName = db.ParseObjectName(viewName, schemaName);
            var view = _showViews(objName.Name, objName.SchemaName).FirstOrDefault();
            if (view == null) return view;
            string sql = $@"
select 
	序号=ROW_NUMBER() OVER(order BY ORDINAL_POSITION),
	列名=T.COLUMN_NAME,
	类型=T.DATA_TYPE,
    T.CHARACTER_MAXIMUM_LENGTH,
	T.NUMERIC_PRECISION,
	T.NUMERIC_PRECISION_RADIX,
	T.NUMERIC_SCALE,
	T.DATETIME_PRECISION,
    是否可空=T.IS_NULLABLE,
	说明=tt.说明  
from INFORMATION_SCHEMA.COLUMNS T
--关联列说明
left outer join
(select 列名=c.name,说明=p.value
from sys.columns c 
	left outer join sys.extended_properties p on c.column_id = p.minor_id and c.object_id=p.major_id
where c.object_id=OBJECT_ID('{objName.NormalName}'))  tt
on t.COLUMN_NAME=tt.列名
where t.TABLE_NAME='{objName.Name}' and t.TABLE_SCHEMA='{objName.SchemaName}'";
            DataTable dt = db.SelectDataTable(sql);
            List<Column> columns = new List<Column>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0, len = dt.Rows.Count; i < len; i++)
                {
                    var column = new SqlServerColumn();
                    column.Name = dt.Rows[i]["列名"].ToString();
                    column.TableName = viewName;
                    //列类型
                    column.DATA_TYPE = dt.Rows[i]["类型"].ToString();
                    column.CHARACTER_MAXIMUM_LENGTH = dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    column.NUMERIC_PRECISION = dt.Rows[i]["NUMERIC_PRECISION"].ToString();
                    column.DATETIME_PRECISION = dt.Rows[i]["DATETIME_PRECISION"].ToString();
                    column.NUMERIC_SCALE = dt.Rows[i]["NUMERIC_SCALE"].ToString();
                    column.ConvertType();
                    //列说明
                    column.Desc = dt.Rows[i]["说明"].ToString();
                    //是否可空
                    column.IsNullAble = dt.Rows[i]["是否可空"].ToString() == "NO" ? false : true;
                    columns.Add(column);
                }
            }
            view.Columns = columns;
            return view;
        }
        /// <summary>
        /// 返回所有的视图(仅用户创建的视图)
        /// </summary>
        /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
        /// <returns></returns>
        public override List<View> ShowViews(string schemaName = null)
        {
            schemaName = db.DealSqlInject(schemaName);
            return _showViews(null, schemaName);
        }

        private List<View> _showViews(string viewName, string schemaName = null)
        {
            List<View> views = new List<View>();
            var sql = @"select infov.*,sysv.create_date,sysv.modify_date 
from INFORMATION_SCHEMA.VIEWS infov 
	left join sys.views sysv on infov.TABLE_NAME=sysv.name and infov.TABLE_SCHEMA=(select name from sys.schemas where schema_id=sysv.schema_id)
where sysv.type='V'";
            if (viewName.IsNotNullOrEmptyOrWhiteSpace()) sql += $" and TABLE_NAME='{viewName}'";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace()) sql += $" and TABLE_SCHEMA='{schemaName}'";
            var dt = db.SelectDataTable(sql);
            for (int i = 0, len = dt.Rows.Count; i < len; i++)
            {
                var view = new SqlServerView()
                {
                    DataBaseName = dt.Rows[i]["TABLE_CATALOG"].ToString(),
                    Name = dt.Rows[i]["TABLE_NAME"].ToString(),
                    CreateSql = dt.Rows[i]["VIEW_DEFINITION"].ToString(),
                    SchemaName = dt.Rows[i]["TABLE_SCHEMA"].ToString()
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    view.CreateTime = DateTime.Parse(createTime);
                }
                var updatetime = dt.Rows[i]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    view.LastUpdate = DateTime.Parse(updatetime);
                }
                views.Add(view);
            }
            if (views.Count > 0)
            {
                //视图说明
                sql = views.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'view', '{v.Name}',null,null) t")
                       .ToStringSeparated("\r\nunion\r\n");
                dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var viewname = dt.Rows[i]["objname"].ToString();
                    var desc = dt.Rows[i]["value"]?.ToString();
                    var v = views.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                    ((SqlServerView)v).Desc = desc;
                }
            }
            return views;
        }
        #endregion

        #region 存储过程&函数&序列
        /// <summary>
        /// 返回所有的序列
        /// </summary>
        /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
        /// <returns></returns>
        public override List<Sequence> ShowSequences(string schemaName = null)
        {
            schemaName = db.DealSqlInject(schemaName);
            if (!db.IsSqlServerVersion2012Compatible())
            {
                //SqlServer 2012推出序列
                return new List<Sequence>();
            }
            return _showSequences(null, schemaName);
        }

        /// <summary>
        /// 返回所有的序列
        /// </summary>
        /// <param name="schemaName">指定schema名称</param>
        /// <param name="seqName">指定序列名称</param>
        /// <returns></returns>
        private List<Sequence> _showSequences(string seqName, string schemaName = null)
        {
            var res = new List<Sequence>();
            var sql = @"select * from (select 
	SCHEMA_NAME(infos.schema_id) as SEQUENCE_SCHEMA,
	infos.name as SEQUENCE_NAME,
	ISnull(TYPE_NAME(infos.system_type_id),TYPE_NAME(infos.user_type_id)) as DATA_TYPE,
	infos.MINIMUM_VALUE,
	infos.MAXIMUM_VALUE,
	infos.START_VALUE,
	infos.INCREMENT,
	infos.CURRENT_VALUE,
	infos.is_cycling as CYCLE_OPTION,
	infos.IS_CACHED,
	infos.CACHE_SIZE,
infos.create_date,infos.modify_date 
from sys.sequences infos) as infos
where 1=1 ";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and infos.SEQUENCE_SCHEMA='{schemaName.Replace("'", "''")}'";
            }
            if (seqName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and infos.SEQUENCE_NAME='{seqName.Replace("'", "''")}'";
            }
            var dt = db.SelectDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var seq = new SqlServerSequence()
                {
                    DataBaseName = db.DBName,
                    SchemaName = dt.Rows[i]["SEQUENCE_SCHEMA"].ToString(),
                    Name = dt.Rows[i]["SEQUENCE_NAME"].ToString(),
                    Type = dt.Rows[i]["DATA_TYPE"].ToString(),
                    IsCached = dt.Rows[i]["IS_CACHED"].To<bool>(),
                    CacheSize = dt.Rows[i]["CACHE_SIZE"].ToWithDefault<int?>(null),
                    Increment = long.Parse(dt.Rows[i]["INCREMENT"].ToString()),
                    CurrentValue = long.Parse(dt.Rows[i]["CURRENT_VALUE"].ToString()),
                    IsCycle = dt.Rows[i]["CYCLE_OPTION"].ToString() == "True",
                    StartValue = long.Parse(dt.Rows[i]["START_VALUE"].ToString()),
                    MaxValue = long.Parse(dt.Rows[i]["MAXIMUM_VALUE"].ToString()),
                    MinValue = long.Parse(dt.Rows[i]["MINIMUM_VALUE"].ToString())
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    seq.CreateTime = DateTime.Parse(createTime);
                }
                var updatetime = dt.Rows[i]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(updatetime))
                {
                    seq.LastUpdate = DateTime.Parse(updatetime);
                }
                res.Add(seq);
            }
            return res;
        }

        /// <summary>
        /// 返回所有的存储过程(仅用户自定义的)
        /// </summary>
        /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
        /// <returns></returns>
        public override List<Procedure> ShowProcedures(string schemaName = null)
        {
            schemaName = db.DealSqlInject(schemaName);
            return _showProcedures(null, schemaName);
        }

        private List<Procedure> _showProcedures(string procName, string schemaName = null)
        {
            List<Procedure> res = new List<Procedure>();
            string sql = @"select infor.ROUTINE_CATALOG, infor.ROUTINE_SCHEMA,infor.ROUTINE_NAME,infor.ROUTINE_DEFINITION,infor.CREATED,infor.LAST_ALTERED
from INFORMATION_SCHEMA.ROUTINES infor where ROUTINE_TYPE='PROCEDURE'";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and ROUTINE_SCHEMA='{schemaName.Replace("'", "''")}'";
            }
            if (procName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and ROUTINE_NAME='{procName.Replace("'", "''")}'";
            }
            DataTable dt = db.SelectDataTable(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var proc = new SqlServerProcedure()
                    {
                        DataBaseName = dt.Rows[i]["ROUTINE_CATALOG"].ToString(),
                        SchemaName = dt.Rows[i]["ROUTINE_SCHEMA"].ToString(),
                        Name = dt.Rows[i]["ROUTINE_NAME"].ToString(),
                        CreateSql = dt.Rows[i]["ROUTINE_DEFINITION"].ToString()
                    };
                    var createTime = dt.Rows[i]["CREATED"].ToString();
                    if (!string.IsNullOrWhiteSpace(createTime))
                    {
                        proc.CreateTime = DateTime.Parse(createTime);
                    }
                    var updatetime = dt.Rows[i]["LAST_ALTERED"].ToString();
                    if (!string.IsNullOrWhiteSpace(updatetime))
                    {
                        proc.LastUpdate = DateTime.Parse(updatetime);
                    }
                    res.Add(proc);
                }
            }
            if (res.Count > 0)
            {
                //存储过程说明
                sql = res.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'PROCEDURE', '{v.Name}',null,null) t")
                       .ToStringSeparated("\r\nunion\r\n");
                dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var viewname = dt.Rows[i]["objname"].ToString();
                    var desc = dt.Rows[i]["value"]?.ToString();
                    var v = res.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                    ((SqlServerProcedure)v).Desc = desc;
                }
            }
            return res;
        }

        /// <summary>
        /// 返回所有的函数(仅用户自定义的)
        /// </summary>
        /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
        /// <returns></returns>
        public override List<Function> ShowFunctions(string schemaName = null)
        {
            schemaName = db.DealSqlInject(schemaName);
            return _showFunctions(null, schemaName);
        }

        private List<Function> _showFunctions(string funcName, string schemaName = null)
        {
            var sql = $@"select syso.name,syso.type,syso.type_desc,syso.create_date,syso.modify_date,infor.ROUTINE_CATALOG,infor.ROUTINE_SCHEMA,infor.ROUTINE_DEFINITION
from sys.objects syso left join INFORMATION_SCHEMA.ROUTINES infor on syso.name=infor.ROUTINE_NAME
where type in ('FN','TF','IF')
";
            if (schemaName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and ROUTINE_SCHEMA='{schemaName.Replace("'", "''")}'";
            }
            if (funcName.IsNotNullOrEmptyOrWhiteSpace())
            {
                sql += $" and name='{funcName.Replace("'", "''")}'";
            }
            var dt = db.SelectDataTable(sql);
            var res = new List<Function>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var func = new SqlServerFunction()
                {
                    DataBaseName = dt.Rows[i]["ROUTINE_CATALOG"].ToString(),
                    SchemaName = dt.Rows[i]["ROUTINE_SCHEMA"].ToString(),
                    Name = dt.Rows[i]["name"].ToString(),
                    Type = dt.Rows[i]["type"].ToString(),
                    TypeString = dt.Rows[i]["type_desc"].ToString(),
                    CreateSql = dt.Rows[i]["ROUTINE_DEFINITION"].ToString()
                };
                var createTime = dt.Rows[i]["create_date"].ToString();
                if (!string.IsNullOrWhiteSpace(createTime))
                {
                    func.CreateTime = DateTime.Parse(createTime);
                }
                var modify_date = dt.Rows[i]["modify_date"].ToString();
                if (!string.IsNullOrWhiteSpace(modify_date))
                {
                    func.LastUpdate = DateTime.Parse(modify_date);
                }
                res.Add(func);
            }
            if (res.Count > 0)
            {
                //函数说明
                sql = res.Select(v => $"SELECT objname='{v.SchemaName}.'+t.objname,t.value FROM fn_listextendedproperty ('MS_Description', 'schema', '{v.SchemaName}', 'FUNCTION', '{v.Name}',null,null) t")
                       .ToStringSeparated("\r\nunion\r\n");
                dt = db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var viewname = dt.Rows[i]["objname"].ToString();
                    var desc = dt.Rows[i]["value"]?.ToString();
                    var v = res.FirstOrDefault(v => $"{v.SchemaName}.{v.Name}" == viewname);
                    ((SqlServerFunction)v).Desc = desc;
                }
            }
            return res;
        }
        #endregion

        #region 显示数据库的备份记录 ShowBackUpHistory()
        /// <summary>
        /// 显示数据库的备份记录
        /// </summary>
        /// <returns></returns>
        public List<SqlServerBackUpHistory> ShowBackUpHistory()
        {
            var sql = $@"
SELECT 
     bs.backup_set_id,
     bs.database_name,
	 bs.name as backup_set_name,
	 bs.user_name,
	 bs.expiration_date,
     bs.backup_start_date,
     bs.backup_finish_date,
     CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
     CAST(DATEDIFF(second, bs.backup_start_date,
     bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
     CASE bs.[type]
         WHEN 'D' THEN 'Full Backup'
         WHEN 'I' THEN 'Differential Backup'
         WHEN 'L' THEN 'TLog Backup'
         WHEN 'F' THEN 'File or filegroup'
         WHEN 'G' THEN 'Differential file'
         WHEN 'P' THEN 'Partial'
         WHEN 'Q' THEN 'Differential Partial'
     END AS BackupType,
     bmf.physical_device_name,
     CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
     CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
     bs.server_name,
     bs.recovery_model
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 where database_name='{db.DBName}'
 ORDER BY bs.backup_start_date;";
            return db.SelectModelList<SqlServerBackUpHistory>(sql);
        }
        #endregion

        #region 显示数据库的恢复记录 ShowRestoreHistory()
        /// <summary>
        /// 显示数据库的恢复记录
        /// </summary>
        /// <returns></returns>
        public List<SqlServerRestoreHistory> ShowRestoreHistory()
        {
            var sql = $@"
SELECT 
     rs.[restore_history_id],
     rs.[restore_date],
     rs.[destination_database_name],
     bmf.physical_device_name,
	 bs.name as backup_set_name,
     rs.[user_name],
     rs.[backup_set_id],
     CASE rs.[restore_type]
         WHEN 'D' THEN 'Database'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'V' THEN 'Verifyonly'
     END AS RestoreType,
     rs.[replace],
     rs.[recovery],
     rs.[restart],
     rs.[stop_at],
     rs.[device_count],
     rs.[stop_at_mark_name],
     rs.[stop_before]
FROM [msdb].[dbo].[restorehistory] rs
INNER JOIN [msdb].[dbo].[backupset] bs
--on rs.backup_set_id = bs.media_set_id
ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
ON bs.media_set_id = bmf.media_set_id
where destination_database_name='{db.DBName}'
order by restore_date;";
            return db.SelectModelList<SqlServerRestoreHistory>(sql);
        }
        #endregion

        #region 显示数据库的整体信息 ShowDataBaseInfo()
        /// <summary>
        /// 显示数据库的整体信息
        /// </summary>
        /// <returns></returns>
        public SqlServerDataBaseInfo ShowDataBaseInfo()
        {
            var info = new SqlServerDataBaseInfo();
            //磁盘剩余空间
            var dt = db.SelectDataTable("Exec master.dbo.xp_fixeddrives");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                info.DriverRemainSpaces.Add((dt.Rows[i][0].ToString(), long.Parse(dt.Rows[i][1].ToString())));
            }
            //sp_server_info
            dt = db.SelectDataTable("Exec sp_server_info");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var attr_name = dt.Rows[i]["attribute_name"].ToString();
                var attr_value = dt.Rows[i]["attribute_value"].ToString();
                if (attr_name == "DBMS_NAME")
                {
                    info.DBMS_NAME = attr_value;
                }
                else if (attr_name == "DBMS_VER")
                {
                    info.DBMS_VER = attr_value;
                }
                else if (attr_name == "COLLATION_SEQ")
                {
                    info.Collection_Seq = attr_value;
                }
                else if (attr_name == "SYS_SPROC_VERSION")
                {
                    info.SYS_SPROC_VERSION = attr_value;
                }
            }
            //数据库版本信息
            info.FullVersion = db.SelectScalar<string>("select @@VERSION");
            //sp_spaceused
            var ds = db.SelectDataSet("exec sp_spaceused");
            info.DBName = ds.Tables[0].Rows[0]["databse_name"].ToString();
            info.DBSize = ds.Tables[0].Rows[0]["database_size"].ToString();
            info.UnAllocatedSize = ds.Tables[0].Rows[0]["unallocated space"].ToString();
            info.Reserved = ds.Tables[1].Rows[0]["reserved"].ToString();
            info.DataSize = ds.Tables[1].Rows[0]["data"].ToString();
            info.IndexSize = ds.Tables[1].Rows[0]["index_size"].ToString();
            info.UnUsedSize = ds.Tables[1].Rows[0]["unused"].ToString();
            //DBCC SQLPERF(LOGSPACE)
            dt = db.SelectDataTable("DBCC SQLPERF(LOGSPACE)");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dbname = dt.Rows[i]["Database Name"].ToString();
                var logsize = dt.Rows[i][1].ToString();
                var logpercent = dt.Rows[i][2].ToString();
                if (dbname == db.DBName)
                {
                    info.LogStat = (double.Parse(logsize), double.Parse(logpercent));
                }
            }
            //select * from sys.databases
            var createTime = db.SelectScalar<string>($"select create_date from sys.databases where name='{db.DBName}'");
            if (!string.IsNullOrWhiteSpace(createTime))
            {
                info.CreateTime = DateTime.Parse(createTime);
            }
            return info;
        }
        #endregion

        #region 显示文件组和日志信息 ShowFileGroups()
        /// <summary>
        /// 显示文件组和日志信息
        /// </summary>
        /// <returns></returns>
        public (List<SqlServerFileGroup> FileGroups, List<SqlServerFile> LogFiles) ShowFileGroups()
        {
            List<SqlServerFileGroup> groups = null;
            List<SqlServerFile> files = null;
            groups = db.SelectModelList<SqlServerFileGroup>("select * from sys.filegroups");
            files = db.SelectModelList<SqlServerFile>("select * from sys.database_files");
            for (int i = files.Count - 1; i >= 0; i--)
            {
                var file = files[i];
                var group = groups.Find(g => g.Data_Space_Id == file.Data_Space_Id);
                if (group != null)
                {
                    group.Files.Add(file);
                    files.RemoveAt(i);
                }
            }
            return (groups, files);
        }
        #endregion
    }
}
